package com.wutuo.roster.provider.dao;

import cn.apestar.core.utils.ListUtils;
import com.wutuo.roster.api.request.AuditChildApplyRequest;
import com.wutuo.roster.api.request.SearchRosterRequest;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SqlBuilder;

import java.util.Map;

import static org.apache.ibatis.jdbc.SqlBuilder.*;

public class RosterSqlProvider extends BaseRosterSqlProvider {

    private static final String ROSTER_COLUMNS =
        "a.id,a.grade,a.teacher_id as teacherId,a.cm_institution_id as cmInstitutionId,a.name as name,a.photo as photo,a.sex as sex,a.tg_zt as tgZt,a.year as year";

    public String lisForPage(SearchRosterRequest request) {

        BEGIN();
        SELECT("group_concat(distinct c.tag_id) as tagIds");
        SELECT("group_concat(distinct d.id) as serviceIds");
        SELECT("group_concat(distinct e.id) as parentIds");
        SELECT(ROSTER_COLUMNS);
        FROM("roster a ");
        LEFT_OUTER_JOIN("tag_relations c on a.id = c.roster_id");
        LEFT_OUTER_JOIN("parents e on a.id = e.roster_id");
        LEFT_OUTER_JOIN("user_service d on a.id = d.roster_id");
        getComonJoin(request);

        // 过滤逻辑删除
        WHERE(" a.del_flag = #{delFlag,jdbcType=CHAR}");
        GROUP_BY("a.id");
        ORDER_BY("a.year desc");

        return SQL();
    }

    public String singleList(SearchRosterRequest request) {

        BEGIN();
        SELECT(ROSTER_COLUMNS);
        FROM("roster a ");
        getComonJoin(request);

        // 过滤逻辑删除
        WHERE(" a.del_flag = #{delFlag,jdbcType=CHAR}");
        GROUP_BY("a.id");
        ORDER_BY("a.year desc");

        return SQL();
    }

    public String list(AuditChildApplyRequest request) {

        BEGIN();
        SELECT("group_concat(distinct c.tag_id) as tagIds");
        SELECT("group_concat(distinct d.id) as serviceIds");
        SELECT("group_concat(distinct e.id) as parentIds");
        SELECT(ROSTER_COLUMNS);
        FROM("roster a ");
        LEFT_OUTER_JOIN("tag_relations c on a.id = c.roster_id");
        LEFT_OUTER_JOIN("parents e on a.id = e.roster_id");
        LEFT_OUTER_JOIN("user_service d on a.id = d.roster_id");

        if (request.getCmInstitutionId() != null) {
            WHERE(" a.cm_institution_id = #{cmInstitutionId,jdbcType=BIGINT}");
        }
        if (request.getName() != null) {
            WHERE(" a.name = #{name,jdbcType=BIGINT}");
        }
        if (request.getTeacherId() != null) {
            WHERE(" a.teacher_id = #{teacherId,jdbcType=VARCHAR}");
        }
        if (request.getAccount() != null) {
            WHERE(" e.user_phone = #{account,jdbcType=VARCHAR}");
        }

        WHERE(" a.del_flag =  #{delFlag,jdbcType=CHAR}");
        GROUP_BY("a.id");
        ORDER_BY("a.year desc");
        return SQL();
    }

    public String countTotalChild(SearchRosterRequest request) {

        BEGIN();
        SELECT("count(1)");
        SELECT(ROSTER_COLUMNS);
        FROM("roster a");
        getComonJoin(request);
        // 过滤逻辑删除
        WHERE(" a.del_flag = #{delFlag,jdbcType=CHAR}");
        return SQL();
    }

    public String updateRosterState(Map<String, Object> param) {

        BEGIN();
        SqlBuilder.UPDATE(" roster ");

        if (param.get("type") != null) {
            SqlBuilder.SET("tg_zt = '" + param.get("type") + "'");
        }

        if (param.get("rosterIds") != null) {
            SqlBuilder.WHERE(" id in (" + param.get("rosterIds") + ")");
        }
        return SQL();
    }

    /**
     * 查询家长屁孩列表
     *
     * @param request
     * @return List<RosterResponse>
     * @author lvh 2016年2月3日 下午12:34:20</br>
     */
    public String getChildListByPid(SearchRosterRequest request) {

        BEGIN();
        SELECT(
            "group_concat(e.id) as parentIds,    (case when f.user_id is null then false else true end) as state ");
        SELECT(ROSTER_COLUMNS);
        FROM("roster a");

        StringBuffer leftJoinSql = new StringBuffer();
        leftJoinSql.append("(");
        leftJoinSql.append(" select b.* from child b ,user c ");
        leftJoinSql.append(" where b.user_id = c.id ");
        if (request.getpPhone() != null) {
            leftJoinSql.append("and c.phone = '").append(request.getpPhone()).append("'");
        }
        leftJoinSql.append("and b.del_flag = #{delFlag,jdbcType=CHAR}");
        leftJoinSql.append(") f on a.id = f.roster_id ");

        LEFT_OUTER_JOIN(leftJoinSql.toString());
        LEFT_OUTER_JOIN("parents e on a.id = e.roster_id");

        getComonJoin(request);

        // 过滤逻辑删除
        WHERE(" a.del_flag = #{delFlag,jdbcType=CHAR}");
        GROUP_BY("a.id");
        ORDER_BY("f.user_id,f.update_date");
        return SQL();
    }

    public void getComonJoin(SearchRosterRequest request) {

        if (request.getTeacherId() != null) {
            WHERE(" a.teacher_Id = #{teacherId,jdbcType=VARCHAR}");
        }

        if (request.getName() != null) {
            WHERE(" a.name like CONCAT('%,', #{name}, ',%'))");
        }

        if (request.getYear() != null) {
            WHERE(" a.year = #{year,jdbcType=BIGINT}");
        }

        if (request.getpPhone() != null) {
            WHERE(" e.user_phone = #{pPhone,jdbcType=VARCHAR}");
        }

        if (request.getCmId() != null) {
            WHERE(" a.cm_institution_id = #{cmId,jdbcType=BIGINT}");
        }

        if (request.getCmInstitutionId() != null) {
            WHERE(" a.cm_institution_id = #{cmInstitutionId,jdbcType=BIGINT}");
        }

        if (request.getTgType() != null) {
            WHERE(" d.wt_service_id in (" + request.getTgType() + ")");
        }

        if (request.getId() != null) {
            WHERE(" a.id =#{id}");
        }

        if (request.getTagId() != null) {
            WHERE(" c.tag_id in (" + request.getTagId() + ")");
        }

        if (request.getNoTagId() != null) {
            WHERE(" (c.tag_id not in (" + request.getNoTagId() + ") or c.tag_id is null)");
        }

        if (request.getParentId() != null) {
            WHERE(" f.user_id = #{parentId,jdbcType=BIGINT}");
        }

        if (request.getIsOut()) {
            WHERE(" f.user_id is null");
        }

        if (!StringUtils.isEmpty(request.getTgZt())) {
            WHERE(" a.tg_zt = #{tgZt}");
        }

        if (CollectionUtils.isNotEmpty(request.getRosterIds())) {
            String rosterIds = ListUtils.listToString(request.getRosterIds(), ',');
            WHERE("a.id in (" + rosterIds + ")");
        }

        if (request.getCreateMonth() != null) {
            WHERE("a.create_date = #{createDate,jdbcType=TIMESTAMP}");
        }

        if (request.getCreateMonth() != null) {
            WHERE(
                " date_format(a.create_date,'%Y-%m') = date_format(#{createDate,jdbcType=TIMESTAMP},'%Y-%m')");
        }
    }
}